﻿// -----------------------------------------------------------------------
// <copyright file="DbUtility.cs" company="">
// TODO: Update copyright text.
// </copyright>
// -----------------------------------------------------------------------

namespace HabitAnalyzer
{
    using System;
    using System.Collections.Generic;
    using System.Data.Common;
    using System.Data.Linq;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms.DataVisualization.Charting;



    /// <summary>
    /// Data Access Layer class
    /// </summary>
    public class DbUtility
    {
        public bool DatabaseExists()
        {
            using (var context = new HabitDbDataContext())
            {
                return context.DatabaseExists();
            }
        }

        public void CreateDatabase()
        {
            using (var context = new HabitDbDataContext())
            {
                context.CreateDatabase();
            }
        }

        /// <summary>
        /// Gets the categories.
        /// </summary>
        /// <returns></returns>
        public List<Category> GetCategories()
        {
            using (var context = new HabitDbDataContext())
            {
                var categories = (from c in context.Categories
                                  select c).ToList();

                return categories;
            }
        }

        /// <summary>
        /// Gets the activities.
        /// </summary>
        /// <returns></returns>
        public List<Acitivity> GetActivities()
        {
            using (var context = new HabitDbDataContext())
            {
                var activities = (from a in context.Acitivities
                                  select a).ToList();

                return activities;
            }
        }

        /// <summary>
        /// Inserts the activity.
        /// </summary>
        /// <param name="categoryId">The category id.</param>
        /// <param name="name">The name.</param>
        public void InsertActivity(int categoryId, string name)
        {
            using (var context = new HabitDbDataContext())
            {
                Acitivity act = new Acitivity
                                    {
                                        CateogryID = categoryId,
                                        Name = name
                                    };

                context.Acitivities.InsertOnSubmit(act);
                context.SubmitChanges();
            }
        }

        /// <summary>
        /// Inserts the category.
        /// </summary>
        /// <param name="name">The name.</param>
        public void InsertCategory(string name)
        {
            using (var context = new HabitDbDataContext())
            {
                Category cat = new Category
                                {
                                    Name = name
                                };
                context.Categories.InsertOnSubmit(cat);
                context.SubmitChanges();
            }
        }

        /// <summary>
        /// Inserts the category.
        /// </summary>
        /// <param name="name">The name.</param>
        public void InsertSessionActivity(int sessionId, int categoryId, int activityId, DateTime startDate, DateTime endDate)
        {
            using (var context = new HabitDbDataContext())
            {
                
                SessionActivity sa = new SessionActivity
                {
                  SessionID = sessionId,
                  CategoryID = categoryId,
                  ActivityID = activityId,
                  StartDateTime = startDate,
                  EndDateTime = endDate
                };
                context.SessionActivities.InsertOnSubmit(sa);
                context.SubmitChanges();
            }
        }

        public void InsertSession(string firstName, string lastName, DateTime startDate, DateTime endDate)
        {
            using (var context = new HabitDbDataContext())
            {

                Session s = new Session
                {
                    FirstName = firstName,
                    LastName = lastName,
                    StartDate = startDate,
                    EndDate = endDate
                };
                context.Sessions.InsertOnSubmit(s);
                context.SubmitChanges();
            }
        }

        public List<SessionActivity> GetActivitiesBetweenDates(int sessionId, DateTime start, DateTime end)
        {
            using (var context = new HabitDbDataContext())
            {
                DataLoadOptions dlo = new DataLoadOptions();
                dlo.LoadWith<SessionActivity>(sa => sa.Acitivity);
                dlo.LoadWith<Acitivity>(a => a.Category);
                context.LoadOptions = dlo;
                
                var activities = (from sa in context.SessionActivities
                                  where (sa.SessionID == sessionId && (sa.StartDateTime >= start && sa.EndDateTime < end))
                                  select sa).ToList();

                return activities;
            }
        }

        public IEnumerable<object> GetActivityRemindersDaily(int sessionId)
        {
            using (var context = new HabitDbDataContext())
            {
                DataLoadOptions dlo = new DataLoadOptions();
                dlo.LoadWith<SessionActivity>(sa => sa.Acitivity);
                dlo.LoadWith<Acitivity>(a => a.Category);
                context.LoadOptions = dlo;

                TimeSpan days = new TimeSpan(1, 0, 0, 0);
                DateTime today = DateTime.Now;
                today = today - days;
                DateTime yesterday = DateTime.Now;
                days = new TimeSpan(2, 0, 0, 0);
                yesterday = yesterday - days;

                var activities = (from sa in context.SessionActivities
                                  where (sa.SessionID == sessionId && (sa.StartDateTime.Date == today.Date))
                                  select
                                    new
                                    {
                                        sa.Acitivity.Name
                                    }
                                  ).ToList();
                
                var acts = (from sa in context.SessionActivities
                            where (sa.SessionID == sessionId && (sa.StartDateTime.Date == yesterday.Date))
                            select
                            new
                            {
                                sa.Acitivity.Name
                            }).ToList();

                var reminders = activities.Intersect(acts);

                return reminders;
            }
        }

        public IEnumerable<object> GetActivityRemindersWeekly(int sessionId)
        {
            using (var context = new HabitDbDataContext())
            {
                DataLoadOptions dlo = new DataLoadOptions();
                dlo.LoadWith<SessionActivity>(sa => sa.Acitivity);
                dlo.LoadWith<Acitivity>(a => a.Category);
                context.LoadOptions = dlo;

                TimeSpan days = new TimeSpan(7, 0, 0, 0);
                DateTime oneWeekAgo = DateTime.Now;
                oneWeekAgo = oneWeekAgo - days;
                DateTime twoWeeksAgo = DateTime.Now;
                days = new TimeSpan(14, 0, 0, 0);
                twoWeeksAgo = twoWeeksAgo - days;

                var activities = (from sa in context.SessionActivities
                                  where (sa.SessionID == sessionId && (sa.StartDateTime.Date == oneWeekAgo.Date))
                                  select
                                    new
                                    {
                                        sa.Acitivity.Name
                                    }
                                  ).ToList();

                var acts = (from sa in context.SessionActivities
                            where (sa.SessionID == sessionId && (sa.StartDateTime.Date == twoWeeksAgo.Date))
                            select
                            new
                            {
                                sa.Acitivity.Name
                            }).ToList();

                IEnumerable<object> reminders = activities.Intersect(acts);
                IEnumerable<object> dailyReminders = this.GetActivityRemindersDaily(sessionId);
                List<object> lstReminders = reminders.ToList();
                List<object> lstDailyReminders = dailyReminders.ToList();

                lstReminders.RemoveAll(x => lstDailyReminders.Contains(x));

                return reminders;
            }
        }

        public IEnumerable<object> GetActivityRemindersMonthly(int sessionId)
        {
            using (var context = new HabitDbDataContext())
            {
                DataLoadOptions dlo = new DataLoadOptions();
                dlo.LoadWith<SessionActivity>(sa => sa.Acitivity);
                dlo.LoadWith<Acitivity>(a => a.Category);
                context.LoadOptions = dlo;

                int year = DateTime.Now.Year;
                int month = DateTime.Now.Month;
                int day = DateTime.Now.Day;

                DateTime oneMonthAgo = new DateTime(year, month - 1, day);
                DateTime twoMonthsAgo = new DateTime(year, month - 2, day);

                var activities = (from sa in context.SessionActivities
                                  where (sa.SessionID == sessionId && (sa.StartDateTime.Date == oneMonthAgo.Date))
                                  select
                                    new
                                    {
                                        sa.Acitivity.Name
                                    }
                                  ).ToList();

                var acts = (from sa in context.SessionActivities
                            where (sa.SessionID == sessionId && (sa.StartDateTime.Date == twoMonthsAgo.Date))
                            select
                            new
                            {
                                sa.Acitivity.Name
                            }).ToList();

                IEnumerable<object> reminders = activities.Intersect(acts);
                IEnumerable<object> dailyReminders = this.GetActivityRemindersDaily(sessionId);
                IEnumerable<object> weeklyReminders = this.GetActivityRemindersWeekly(sessionId);
                List<object> lstReminders = reminders.ToList();
                List<object> lstDailyReminders = dailyReminders.ToList();
                List<object> lstWeeklyReminders = weeklyReminders.ToList();
               
                lstReminders.RemoveAll(x => lstDailyReminders.Contains(x));
                lstDailyReminders.RemoveAll(x => lstWeeklyReminders.Contains(x));

                return reminders;
            }
        }

        public List<SessionActivity> GetActivitiesBetweenDatesSorted(int sessionId, DateTime start, DateTime end)
        {
            using (var context = new HabitDbDataContext())
            {
                DataLoadOptions dlo = new DataLoadOptions();
                dlo.LoadWith<SessionActivity>(sa => sa.Acitivity);
                dlo.LoadWith<Acitivity>(a => a.Category);
                context.LoadOptions = dlo;

                var activities = (from sa in context.SessionActivities
                                  where (sa.SessionID == sessionId && (sa.StartDateTime >= start && sa.EndDateTime < end))
                                  orderby sa.StartDateTime select sa).ToList();

                return activities;
            }
        }

        public List<Acitivity> GetActivitiesByCategory(int categoryId)
        {
            using (var context = new HabitDbDataContext())
            {
                var activities = (from a in context.Acitivities
                                  where (a.CateogryID == categoryId)
                                  select a).ToList();

                return activities;
            }
        }

        public List<Category> GetCategoryById(int id)
        {
            using (var context = new HabitDbDataContext())
            {
                var category = (from c in context.Categories
                                where c.CategoryID == id
                                select c).ToList();

                return category;
            }
        }

        public List<Acitivity> GetActivityById(int id)
        {
            using (var context = new HabitDbDataContext())
            {
                var activity = (from a in context.Acitivities
                                where a.ActivityID == id
                                select a).ToList();

                return activity;
            }
        }

        public Session GetSessionById(int id)
        {
            using (var context = new HabitDbDataContext())
            {
                var ses = (from s in context.Sessions
                           where s.SessionID == id
                           select s).First();

                return ses;
            }
        }

        public IEnumerable<object> GetSessionsForListBox()
        {
            using (var context = new HabitDbDataContext())
            {
                var ses = (from s in context.Sessions
                               select new
                               {
                                   SessionID = s.SessionID,
                                   SessionName = s.FirstName.ToString() + " " + s.LastName.ToString() + ": " + s.StartDate.ToString() + " to " + s.EndDate.ToString()
                               }).ToList();

                return ses;
            }
        }

        public List<SessionActivity> GetActivitiesBetweenDatesByCategory(int sessionId, DateTime start, DateTime end, int cateoryId )
        {
            using (var context = new HabitDbDataContext())
            {
                DataLoadOptions dlo = new DataLoadOptions();
                dlo.LoadWith<SessionActivity>(sa => sa.Acitivity);
                dlo.LoadWith<Acitivity>(a => a.Category);
                context.LoadOptions = dlo;

                var activities = (from sa in context.SessionActivities
                                  where (sa.SessionID == sessionId && (sa.StartDateTime >= start && sa.EndDateTime < end) && sa.CategoryID == cateoryId)
                                  select sa).ToList();

                return activities;
            }
        }
        public List<SessionActivity> GetActivitiesBetweenDatesByCategorySorted(int sessionId, DateTime start, DateTime end, int cateoryId)
        {
            using (var context = new HabitDbDataContext())
            {
                DataLoadOptions dlo = new DataLoadOptions();
                dlo.LoadWith<SessionActivity>(sa => sa.Acitivity);
                dlo.LoadWith<Acitivity>(a => a.Category);
                context.LoadOptions = dlo;

                var activities = (from sa in context.SessionActivities
                                  where (sa.SessionID == sessionId && (sa.StartDateTime >= start && sa.EndDateTime < end) && sa.CategoryID == cateoryId)
                                  orderby sa.StartDateTime select sa).ToList();

                return activities;
            }
        }



    }
}
